import pymysql
from pymysql import cursors


def get_connection():
    # 连接数据库
    conn = pymysql.connect(host='localhost',
                           user='root',
                           password='admin',
                           db='cov',
                           cursorclass=cursors.DictCursor,
                           charset='utf8')
    cursor = conn.cursor()
    return conn, cursor


def close_connection(conn, cursor):
    # 关闭数据库
    cursor.close()
    conn.close()


def findAll():
    # 查询所有数据
    conn, cursor = get_connection()
    sql = "SELECT * FROM hotSearch"
    cursor.execute(sql)
    result = cursor.fetchall()
    close_connection(conn, cursor)
    return result

def findByWord(word):
    # 根据省份查询数据
    conn, cursor = get_connection()
    sql = "SELECT * FROM hotSearch WHERE content like %s"
    cursor.execute(sql, '%'+word+'%')
    result = cursor.fetchall()
    close_connection(conn, cursor)
    return result


def delete(id):
    # 根据id删除数据
    conn, cursor = get_connection()
    sql = "DELETE FROM hotSearch WHERE id = %s"
    cursor.execute(sql, id)
    conn.commit()
    close_connection(conn, cursor)


def add(data):
    # 添加数据
    conn, cursor = get_connection()
    sql = "INSERT INTO hotSearch (dt, content) VALUES (%s, %s)"
    cursor.execute(sql, [data['dt'], data['content']])
    conn.commit()
    close_connection(conn, cursor)

def findById(id):
    # 根据id查询数据
    conn, cursor = get_connection()
    sql = "SELECT * FROM hotSearch WHERE id = %s"
    cursor.execute(sql, id)
    result = cursor.fetchone()
    close_connection(conn, cursor)
    return result

def edit(data):
    # 编辑数据
    conn, cursor = get_connection()
    sql = "UPDATE hotSearch SET dt = %s, content = %s WHERE id = %s"
    cursor.execute(sql, [data['dt'], data['content'], data['id']])
    conn.commit()
    close_connection(conn, cursor)
